package vn.student;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import vn.student.mylib.Assignment;
import vn.student.mylib.Class;
import vn.student.mylib.ClassInfoST;
import vn.student.mylib.Contact;
import vn.student.mylib.ContactList;
import vn.student.mylib.Course;
import vn.student.mylib.MyDate;
import vn.student.mylib.Semester;
import vn.student.mylib.WeekDay;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.format.Time;
	/*
		
		Class name: AStudentDB.java
		Function: Lop thu 3 trong mo hinh MVC co tac dung ket noi va thao tac voi CSDL.
		
	*/
public class AStudentDB extends SQLiteOpenHelper {

	private static String DB_PATH = "/data/data/vn.student/databases/";
	
    private static String DB_NAME = "vn_db";

    private SQLiteDatabase myDB;
    
    private final Context context;
    
    private Activity act;
	/*
		Method name: public AStudentDB(Context context).
		Function: Ham constructor khoi tao doi tuong cho lop AStudent dung de ket noi CSDL.
		Parameter: Context hien tai.
		Return: None.
		Note:
    */
    public AStudentDB(Context context){
    	super(context, DB_NAME, null, 1);
    	this.context = context;
    }
    
    /*
		Method name: public void createDadaBase().
		Function: Ham dung de tao co so du lieu cho thiet bi khi lan dau tien chay chuong trinh tren thiet bi.
		Parameter: None.
		Return: None.
		Note:
    */
    public void createDadaBase() throws IOException{
    	boolean dbExist  = checkDataBase();
    	if(dbExist){
    		// do nothing database already exist
    	}else {
			this.getReadableDatabase();
			createAllTable();
		}
    	
    }
	/*
		Method name: private void createAllTable().
		Function: Ham co tac dung khoi tao cac table, trigger trong CSDL de luu tru du lieu cua chuong trinh.
		Parameter: None.
		Return: None.
		Note:
    */
    private void createAllTable(){
    	this.openDatabase();
    	this.execSQL("" +
    			"CREATE TABLE \"Assignment\" (" +
    			"\"A_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ," +
    			"\"Priority\" INTEGER," +
    			"\"Due_Date\" DATETIME NOT NULL  DEFAULT CURRENT_DATE," +
    			"\"Description\" TEXT," +
    			"\"Alert_Time\" DATETIME DEFAULT CURRENT_TIME," +
    			"\"CL_ID\" INTEGER NOT NULL references Class(CL_ID), \"Done\" BOOL not null default 0, \"Name\"  VARCHAR)" +
    			"");
    	this.execSQL("" +
    			"CREATE TABLE \"Cancelled_Class\" ("+
    			"\"CC_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
    			"\"Date\" DATETIME NOT NULL  DEFAULT CURRENT_DATE,"+
    			"\"CL_ID\" INTEGER NOT NULL  references Class(CL_ID))" +
    			"");
    	this.execSQL("" +
    			"CREATE TABLE \"Class\" (\"CL_ID\" INTEGER PRIMARY KEY  NOT NULL ,\"C_ID\"" +
    			" INTEGER NOT NULL ,\"Class_Name\" VARCHAR NOT NULL ,\"Type\" VARCHAR NOT NULL ," +
    			"\"Location\" VARCHAR,\"Date_Start\" DATETIME DEFAULT CURRENT_DATE ,\"Date_End\" " +
    			" DATETIME DEFAULT CURRENT_DATE ,\"Time_Start\" DATETIME DEFAULT CURRENT_TIME ," +
    			"\"Time_End\" DATETIME DEFAULT CURRENT_TIME ,\"Day_repeat\" VARCHAR,\"CT_ID\" INTEGER, " +
    			"\"Repeat\" INTEGER)" +
    			"");
    	this.execSQL("" +
    			"CREATE TABLE \"Contact\" ("+
    			"\"CT_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
    			"\"CT_Name\" VARCHAR NOT NULL ,"+ 
    			"\"Email\" VARCHAR,"+ 
    			"\"PhoneNo\" VARCHAR,"+ 
    			"\"Type\" VARCHAR,"+ 
    			"\"Website\" VARCHAR,"+ 
    			"\"Note\" TEXT,"+ 
    			"\"Address\" VARCHAR," + 
    			"\"Photo\" VARCHAR)" +
    			"");
    	this.execSQL("" +
    			"CREATE TABLE \"Contact_Assignment\"("+ 
    			"\"A_ID\" INTEGER NOT NULL references Assignment(A_ID) ," + 
    			"\"CT_ID\" INTEGER NOT NULL references Contact(CT_ID),"+
    			"PRIMARY KEY (\"CT_ID\", \"A_ID\"))" +
    			"");
    	this.execSQL("" +
    			"CREATE TABLE \"Course\" (\"C_ID\" INTEGER PRIMARY KEY " +
    			" AUTOINCREMENT  NOT NULL , \"S_ID\" INTEGER NOT NULL REFERENCES " +
    			" Semester(S_ID) , \"Date_Start\" DATETIME DEFAULT CURRENT_DATE, " +
    			"\"Date_End\" DATETIME DEFAULT CURRENT_DATE, \"C_Name\" VARCHAR NOT NULL )" +
    			"");
    	this.execSQL("" +
    			"CREATE TABLE \"Exam\" ("+
    			" \"E_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
    			" \"Type\" VARCHAR NOT NULL ,"+
    			" \"Date\" DATETIME NOT NULL DEFAULT CURRENT_DATE , "+ 
    			" \"Time\" INTEGER, "+
    			" \"Location\" VARCHAR, "+ 
    			" \"CL_ID\" INTEGER REFERENCES Class(CL_ID), "+ 
    			" \"C_ID\" INTEGER NOT NULL REFERENCES Course(C_ID) )" +
    			"");
    	this.execSQL("" +
    			"CREATE TABLE \"Holiday\" ( "+
    			" \"H_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "+ 
    			" \"H_Name\" VARCHAR NOT NULL DEFAULT CURRENT_DATE, "+ 
    			" \"Date\" DATETIME NOT NULL )" +
    			"");
    	this.execSQL("" +
    			"CREATE TABLE \"Semester\" (\"S_ID\" INTEGER PRIMARY KEY" +
    			"  AUTOINCREMENT  NOT NULL , \"S_Name\" VARCHAR NOT NULL , " +
    			" \"Date_Start\" DATETIME DEFAULT CURRENT_DATE, \"Date_End\" " +
    			"DATETIME DEFAULT CURRENT_DATE)" +
    			"");
    	this.execSQL("" +
    			"CREATE VIEW \"V_Course\" AS select c.C_ID, cl.CL_ID, a.A_ID"+
    			" from Course c, Class cl, Assignment a, Contact_Assignment ca"+
    			" where c.C_ID=cl.C_ID"+
    			" and cl.CL_ID=a.CL_ID"+
    			" and a.A_ID=ca.A_ID" +
    			"");
    	this.execSQL("" +
    			"CREATE VIEW \"V_Semester\" AS select s.S_ID, c.C_ID, cl.CL_ID, a.A_ID"+
    			" from Semester s, Course c, Class cl, Assignment a, Contact_Assignment ca"+
    			" where s.S_ID=c.S_ID "+
    			" and c.C_ID=cl.C_ID "+
    			" and cl.CL_ID=a.CL_ID "+
    			" and a.A_ID=ca.A_ID" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Assignment_delete "+
    			" after delete on \"Assignment\" for each row "+
    			" begin "+
    			" delete from \"Contact_Assignment\" where \"A_ID\"=old.\"A_ID\"; "+
    			" end" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Assignment_insert_0 BEFORE INSERT" +
    			" ON \"Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
    			" 'insert on table Assignment violates foreign key constraint') " +
    			" WHERE  (SELECT \"CL_ID\" FROM \"Class\" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Assignment_update_0 BEFORE UPDATE ON \"Assignment\" " +
    			" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table Assignment violates" +
    			" foreign key constraint') WHERE  (SELECT \"CL_ID\" FROM \"Class\" WHERE \"CL_ID\" =" +
    			"  NEW.\"CL_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Contact_Assignment_insert_0 BEFORE INSERT ON " +
    			" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
    			" 'insert on table Contact_Assignment violates foreign key constraint')" +
    			"  WHERE  (SELECT \"CT_ID\" FROM \"Contact\" WHERE \"CT_ID\" = NEW.\"CT_ID\") IS NULL; END" +
    			"");
    	
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Contact_Assignment_insert_1 BEFORE INSERT ON" +
    			" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
    			"'insert on table Contact_Assignment violates foreign key constraint') " +
    			"WHERE  (SELECT \"A_ID\" FROM \"Assignment\" WHERE \"A_ID\" = NEW.\"A_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Contact_Assignment_update_0 BEFORE UPDATE ON " +
    			" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
    			" 'update on table Contact_Assignment violates foreign key constraint') " +
    			" WHERE  (SELECT \"CT_ID\" FROM \"Contact\" WHERE \"CT_ID\" = NEW.\"CT_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Contact_Assignment_update_1 BEFORE UPDATE ON" +
    			"  \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
    			" 'update on table Contact_Assignment violates foreign key constraint') " +
    			" WHERE  (SELECT \"A_ID\" FROM \"Assignment\" WHERE \"A_ID\" = NEW.\"A_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Contact_delete"+
    			" after delete on \"Contact\" for each row "+
    			" begin "+
    			" delete from \"Contact_Assignment\" where \"CT_ID\"=old.\"CT_ID\";"+
    			" update \"Class\" set \"CT_ID\"=NULL where \"CT_ID\"=old.\"CT_ID\";  "+
    			" end" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Course_delete"+
    			" after delete on \"Course\" for each row"+
    			" begin"+
    			" delete from \"Contact_Assignment\" where \"A_ID\" in "+
    			" (select \"A_ID\" from V_Course where \"C_ID\"=old.\"C_ID\");"+
    			" delete from \"Assignment\" where \"CL_ID\" in (select CL_ID from \"Class\" where \"C_ID\"=old.\"C_ID\");"+
    			" delete from \"Class\" where \"C_ID\"=old.\"C_ID\";"+
    			" delete from \"Exam\" where \"C_ID\"=old.\"C_ID\";"+
    			" end" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Semester_delete"+
    			" after delete on \"Semester\" for each row"+
    			" begin"+
    			" delete from \"Contact_Assignment\" where \"A_ID\" in "+
    			" (select \"A_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\");"+
    			" delete from \"Assignment\" where \"CL_ID\" in "+
    			" (select \"CL_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\");"+
    			" delete from \"Class\" where \"C_ID\" in  (select \"C_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\"); "+
    			" delete from \"Exam\" where \"C_ID\" in  (select \"C_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\"); "+
    			" delete from \"Course\" where \"S_ID\"=old.\"S_ID\"; "+
    			"end" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Course_insert_0 BEFORE INSERT ON " +
    			" \"Course\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
    			" 'insert on table Course violates foreign key constraint')" +
    			" WHERE  (SELECT \"S_ID\" FROM \"Semester\" WHERE \"S_ID\" = NEW.\"S_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Course_update_0 BEFORE UPDATE ON" +
    			" \"Course\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
    			" 'update on table Course violates foreign key constraint') WHERE " +
    			" (SELECT \"S_ID\" FROM \"Semester\" WHERE \"S_ID\" = NEW.\"S_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Exam_insert_0 BEFORE INSERT ON \"Exam\" " +
    			" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
    			" 'insert on table Exam violates foreign key constraint') WHERE " +
    			" (SELECT \"C_ID\" FROM \"Course\" WHERE \"C_ID\" = NEW.\"C_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Exam_insert_1 BEFORE INSERT ON" +
    			" \"Exam\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
    			" 'insert on table Exam violates foreign key constraint')" +
    			" WHERE  NEW.\"CL_ID\" IS NOT NULL AND  (SELECT \"CL_ID\" FROM \"Class\"" +
    			" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Exam_update_0 BEFORE UPDATE ON" +
    			" \"Exam\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
    			" 'update on table Exam violates foreign key constraint') WHERE " +
    			" (SELECT \"C_ID\" FROM \"Course\" WHERE \"C_ID\" = NEW.\"C_ID\") IS NULL; END" +
    			"");
    	this.execSQL("" +
    			"CREATE TRIGGER _fk_Exam_update_1 BEFORE UPDATE ON \"Exam\" " +
    			" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
    			"'update on table Exam violates foreign key constraint')" +
    			" WHERE  NEW.\"CL_ID\" IS NOT NULL AND  (SELECT \"CL_ID\" FROM \"Class\" " +
    			" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
    			"");
    }
	/*
		Method name: public void dropDatabase().
		Function: Ham co tac dung xoa co so du lieu cua chuong trinh khoi thiet bi.
		Parameter: None.
		Return: None.
		Note:
    */
    public void dropDatabase() throws IOException{
    	boolean dbExist  = checkDataBase();
    	if(dbExist){
    		this.getReadableDatabase();
			try {
				//
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error drop database");
			}
    	}else {
			// do nothing database already exist
		}
    	
    }
	/*
		Method name: public void execSQL(String sql).
		Function: Ham co tac dung thuc thi 1 cau lenh SQL.
		Parameter: None.
		Return: None.
		Note:
    */
    public void execSQL(String sql){
    	this.openDatabase();
    	myDB.execSQL(sql);
    	this.close();
    }
	/*
		Method name: public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy).
		Function: Ham co tac dung thuc thi mot cau lenh truy van CSDL va tra ve mot doi tuong kieu Cursor.
		Parameter: table: Table can truy van, columns: Cot can lay du lieu, 
			selection: Dieu kien loc, selectionArgs: Tham so cho dieu kien loc, 
			groupBy: Dieu kien Group, having: Dieu kien having, orderBy: Dieu kien Order.
		Return: Doi tuong kieu Cursor.
		Note:
    */
    public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy){
    	this.openDatabase();
    	Cursor result = myDB.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
    	return result;
    }
    /*
		Method name: public SQLiteDatabase getDB().
		Function: Ham co tac dung trar ve Database cua chuong trinh.
		Parameter: None.
		Return: Doi tuong kieu SQLiteDatabase.
		Note:
    */
    public SQLiteDatabase getDB(){
    	return myDB;
    }
	public void copyDataBase() throws IOException{
		//Open your local db as the input stream
		InputStream myInput = context.getAssets().open(DB_NAME);
		String outFileName = DB_PATH+DB_NAME;
		OutputStream myOutput = new FileOutputStream(outFileName);
		byte[] buffer = new byte[1024];
		int leng;
		while((leng = myInput.read(buffer))>0){
			myOutput.write(buffer, 0, leng);
		}
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}
	/*
		Method name: private boolean checkDataBase().
		Function: Ham co tac dung kiem tra su ton tai cua Database tren thiet bi.
		Parameter: None.
		Return: True neu co Database tren thiet bi, nguoc lai tra ve False.
		Note:
    */
	private boolean checkDataBase() {
		SQLiteDatabase checkDB= null;
		try {
			String myPath = DB_PATH+DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
		} catch (SQLiteException e) {
			// database doesn't exist yet
		}
		if(checkDB!=null){
			checkDB.close();
		}
		return checkDB!=null? true:false;
	}
	/*
		Method name: public SQLiteDatabase openDatabase().
		Function: Ham co tac dung mo ket noi den Database cua chuong trinh tren thiet bi va tra ve database cua chuong trinh.
		Parameter: None.
		Return: Doi tuong kieu SQLiteDatabase.
		Note:
    */
	public SQLiteDatabase openDatabase() throws SQLException{
		String myPath = DB_PATH + DB_NAME;
		if(!this.checkDataBase())
			try {
				createDadaBase();
			} catch (IOException e) {
				
			}
		myDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
		return myDB;
	}
	/*
		Method name: public synchronized void close().
		Function: Ham co tac dung dong ket noi den CSDL cua chuong trinh.
		Parameter: None.
		Return:.
		Note:
    */
	@Override
	public synchronized void close(){
		if(myDB != null)
			myDB.close();
		super.close();
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}
	/*
		Method name: public ContactList getContactList(String n).
		Function: Ham co tac dung lay ve danh sach instructor hay danh sach partner.
		Parameter: String n: la gv neu can lay ve danh sach Instructor, nguoc lai lay ve Partner.
		Return:.
		Note:
    */
	public ContactList getContactList(String n){
		ContactList list = new ContactList();
		this.openDatabase();
		String[] colums = {"CT_ID","CT_Name","Email","PhoneNo"};
    	String[] parms = {""};
    	if(n.equals("gv"))
    		parms[0]= "TEACHER";
    	else
    		parms[0]= "STUDENT";	
    	Cursor result = myDB.query("Contact", colums, "type=?", parms, null, null, "CT_Name");
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String id = result.getString(0);
        		String name=result.getString(1);
        		String email=result.getString(2);
        		String phoneNo=result.getString(3);
        		list.addContactInfo(new Contact(id,name,email,phoneNo));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public ArrayList<Contact> getPartnerList(String assignmentID).
		Function: Ham co tac dung lay ve danh sach Partner lam chung bai tap co id la tham so truyen vao.
		Parameter: String assignmentID: id cua bai tap can lay danh sach partner.
		Return: Danh sach Partner duoc luu trong ArrayList<Contact>.
		Note:
    */
	public ArrayList<Contact> getPartnerList(String assignmentID){
		ArrayList<Contact> list = new ArrayList<Contact>();
		this.openDatabase();
		Cursor result = null;
    	try{
			result = myDB.rawQuery("SELECT Contact.CT_ID, CT_Name,"+
	    	" Email, PhoneNo, Website, Note, Address, Photo FROM Contact, Contact_Assignment WHERE Contact_Assignment.A_ID="+assignmentID+" AND Contact.CT_ID=Contact_Assignment.CT_ID", null);
	    	if(result==null)
	    		return null;
	    	else{
	        	result.moveToFirst();
	        	while (!result.isAfterLast()) {
	        		String id = result.getString(0);
	        		String name=result.getString(1);
	        		String email=result.getString(2);
	        		String phoneNo=result.getString(3);
	        		String website=result.getString(4);
	        		String note=result.getString(5);
	        		String address=result.getString(6);
	        		String photo=result.getString(7);
	        		list.add(new Contact(id,name,phoneNo,email,website,note,photo,address,""));
	        		result.moveToNext();
	        	}
	        	result.close();
	        	this.close();
	    	}
    	}
    	catch(Exception e){
    		this.close();
    		return null;
    	}
		return list;
	}
	/*
		Method name: public ArrayList<Class> getClassList(String id2).
		Function: Lay ve danh sach Class ma Instructor giang day.
		Parameter: String id2: Id cua giang vien can lay danh sach class.
		Return: Danh sach class chua trong ArrayList<Class>.
		Note:
    */
	public ArrayList<Class> getClassList(String id2){
		ArrayList<Class> list = new ArrayList<Class>();
		this.openDatabase();
		String[] colums = {"CL_ID,Class_Name","Type","Location","Time_Start","Time_End","Repeat","Day_repeat","Date_Start"};
    	String[] parms = {""};	
    	parms[0] = id2;
    	Cursor result = myDB.query("Class", colums, "CT_ID=?", parms, null, null, "Class_Name");
    	result.getCount();
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String id=result.getString(0);
        		String name=result.getString(1);
        		String type=result.getString(2);
        		String location=result.getString(3);
        		String timeStart=result.getString(4).substring(0, 5);
        		String timeEnd=result.getString(5).substring(0, 5);
        		String repeat=result.getString(6);
        		String dateRepeat=result.getString(7);
        		int assCount = this.getAssUndoneCount(id);
        		String dateStart=result.getString(8);
        		list.add(new Class(id,name,type,location,timeStart,timeEnd,repeat,dateRepeat,dateStart,assCount));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public ArrayList<Class> getCourseClassList(String courseID).
		Function: Lay ve danh sach lop mon hoc cua mot mon hoc.
		Parameter: String courseID: Id cua mon hoc muon lay ve danh sach lop mon hoc.
		Return: Danh sach lop mon hoc can lay chua trong ArrayList<Class>.
		Note:
    */
	public ArrayList<Class> getCourseClassList(String courseID){
		ArrayList<Class> list = new ArrayList<Class>();
		this.openDatabase();
		String[] colums = {"CL_ID,Class_Name","Type","Location","Time_Start","Time_End","Repeat","Day_repeat","Date_Start"};
    	String[] parms = {""};	
    	parms[0] = courseID;
    	Cursor result = myDB.query("Class", colums, "C_ID=?", parms, null, null, "Class_Name");
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String id=result.getString(0);
        		String name=result.getString(1);
        		String type=result.getString(2);
        		String location=result.getString(3);
        		String timeStart=result.getString(4).substring(0, 5);
        		String timeEnd=result.getString(5).substring(0, 5);
        		String repeat=result.getString(6);
        		String dateRepeat=result.getString(7);
        		int assCount = this.getAssUndoneCount(id);
        		String dateStart = result.getString(8);
        		list.add(new Class(id,name,type,location,timeStart,timeEnd,repeat,dateRepeat,dateStart,assCount));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public ArrayList<ClassInfoST> getTodayClassList().
		Function: Lay ve danh sach lop mon hoc cua ngay hien tai.
		Parameter: None.
		Return: None.
		Note:
    */
	public ArrayList<ClassInfoST> getTodayClassList(){
		ArrayList<ClassInfoST> list = new ArrayList<ClassInfoST>();
		String[] colums = {"CL_ID","Class_Name","Type","Location","Time_Start","Time_End","Repeat","Day_repeat"};
		String[] parms = {"","",""};  
		Time currentTime = new Time();
		currentTime.setToNow();
		String tmp = "";
		int weekDay = currentTime.weekDay;
    	switch(weekDay){
    		case 0:
    			tmp = "SUN";
    			break;
    		case 1:
    			tmp = "MON";
        		break;
    		case 2:
    			tmp = "TUE";
        		break;
    		case 3:
    			tmp = "WED";
        		break;
    		case 4:
    			tmp = "THU";
        		break;
    		case 5:
    			tmp = "FRI";
        		break;
    		case 6:
    			tmp = "SAT";
        		break;
    	}
    	parms[0] = tmp;
    	parms[1] = tmp;
    	parms[2] = tmp;
		this.openDatabase();
		Cursor result;
		try{
			result = myDB.query("Class", colums,
					"( Date_Start <= CURRENT_DATE AND Date_End >= CURRENT_DATE ) AND "+
					"(" +
					"Repeat=0 OR " +
					"(Repeat = 2 AND (?!='SUN' OR ?!='SAT')) OR" +
					"(Repeat=1 and Day_repeat=?)" +
					")"
					, parms, null, null, "Time_Start");
		}
		catch(Exception e){
			return null;
		}
		if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String id=result.getString(0);
        		String name=result.getString(1);
        		String type=result.getString(2);
        		String location=result.getString(3);
        		String timeStart=result.getString(4).substring(0, 5);
        		String timeEnd=result.getString(5).substring(0, 5);
        		String repeat=result.getString(6);
        		String dateRepeat=result.getString(7);
        		int assCount = this.getAssUndoneCount(id);
        		list.add(new ClassInfoST(id,name,type,location,timeStart,timeEnd,repeat,dateRepeat,assCount));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: private int getAssUndoneCount(String id).
		Function: Dem so bai tap chua hoan tat cua mot lop mon hoc.
		Parameter: String id: Id cua lop mon hoc muon lay danh sach bai tap chua hoan tat.
		Return: So bai tap chua hoan tat cua lop mon hoc muon lay.
		Note:
    */
	private int getAssUndoneCount(String id){
		this.openDatabase();
		Cursor result;
		int assCount = 0;
		try{
			result = myDB.rawQuery("SELECT COUNT(A_ID) FROM Assignment WHERE CL_ID="+id+" AND Done=0",null);
		}
		catch(Exception e){
			return assCount;
		}
    	if(result==null)
    		return assCount;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		assCount = result.getInt(0);
        		result.moveToNext();
        	}
    	}
    	this.close();
    	return assCount;
	}
	/*
		Method name: public ArrayList<Assignment> getAssignmentList(String id).
		Function: Lay ve danh sach bai tap lam chung voi partner co id truyen vao.
		Parameter: String id: Id cua partner can tim danh sach bai tap lam chung.
		Return: None.
		Note:
    */
	public ArrayList<Assignment> getAssignmentList(String id){
		ArrayList<Assignment> list = new ArrayList<Assignment>();
		this.openDatabase();
		Cursor result;
		try{
			result = myDB.rawQuery("SELECT Assignment.A_ID,Name, Priority, Description, Due_Date, Done,Assignment.CL_ID,Alert_Time FROM Assignment, Contact_Assignment WHERE Contact_Assignment.CT_ID="+id+" AND Assignment.A_ID=Contact_Assignment.A_ID ORDER BY Due_Date DESC",null);
		}
		catch(Exception e){
			return null;
		}
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String assID=result.getString(0);
        		String name=result.getString(1);
        		int priority = result.getInt(2);
        		String des = result.getString(3);
        		String dueDate=result.getString(4);
        		boolean done = result.getString(5).equals("1")?true:false;
        		String classID=result.getString(6);
        		String alertTime=result.getString(7);
        		list.add(new Assignment(assID,name,priority,des,dueDate,alertTime,done,classID));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public Class getClassInfo(String id).
		Function: Lay ve thong tin cua mot lop mon hoc.
		Parameter: String id: ID cua lop mon hoc can lay ve thong tin.
		Return: None.
		Note:
    */
	public Class getClassInfo(String id){
		Class classInfo = null;
		this.openDatabase();
		try{
			Cursor result = myDB.rawQuery("SELECT Class_Name,Class.Type,Location,Time_Start,Time_End,Date_Start,Date_End,Repeat,Day_repeat,C_ID,CT_ID FROM Class WHERE CL_ID="+id+"",null);
			if(result==null)
	    		return null;
	    	else{
	        	result.moveToFirst();
	        	while (!result.isAfterLast()) {
	        		String name=result.getString(0);
	        		String type=result.getString(1);
	        		String location=result.getString(2);
	        		String timeStart=result.getString(3).substring(0, 5);
	        		String timeEnd=result.getString(4).substring(0, 5);
	        		String dateStart=result.getString(5);
	        		String dateEnd=result.getString(6);
	        		String repeat=result.getString(7);
	        		String dateRepeat=result.getString(8);
	        		String courseID=result.getString(9);
	        		String instructorID=result.getString(10);
	        		String instructorName=null;
	        		if(instructorID!=null&&instructorID.length()>0){
	        			Cursor result2 = myDB.rawQuery("SELECT CT_Name FROM Contact WHERE CT_ID="+instructorID, null);
	        			if(result2!=null){
	        				result2.moveToFirst();
	        	        	while (!result2.isAfterLast()) {
	        	        		instructorName=result2.getString(0);
	        	        		result2.moveToNext();
	        	        	}
	        			}
	        		}
	        		int assCount = this.getAssUndoneCount(id);
	        		classInfo = new Class(id,name,type,courseID,location,dateStart,dateEnd,timeStart,timeEnd,repeat,dateRepeat,instructorID,instructorName,assCount);
	        		result.moveToNext();
	        	}
	        	result.close();	
	    	}
		}
		catch(Exception e){
			this.close();
			return null;
		}
    	this.close();
		return classInfo;
	}
	/*
		Method name: public long addNewContact(Contact contactInfo).
		Function: Them moi mot doi tuong Contact vao CSDL.
		Parameter: Contact contactInfo: Doi tuong Contact muon them vao CSDL.
		Return: id cua doi tuong Contact moi them vao CSDL.
		Note:
    */
	public long addNewContact(Contact contactInfo){
		this.openDatabase();
		ContentValues cv=new ContentValues();
		long id = -1;
		cv.put("CT_Name", contactInfo.getName());
		cv.put("Email", contactInfo.getEmail());
		cv.put("PhoneNo", contactInfo.getPhoneNo());
		cv.put("Type", contactInfo.type);
		cv.put("Website", contactInfo.website);
		cv.put("Note", contactInfo.note);
		cv.put("Address", contactInfo.address);
		cv.put("Photo", contactInfo.photo);
		try{
			id = myDB.insert("Contact",null, cv);
			if(id==-1){
				this.close();
				return -1;
			}
		}
		catch(Exception e){
			this.close();
			return -1;
		}
		this.close();
		return id;
	}
	/*
		Method name: public boolean removeSemester(String id).
		Function: Xoa mot hoc ki ra khoi CSDL.
		Parameter: String id: id cua hoc ki can xoa.
		Return: True neu xoa thanh cong, nguoc lai la false.
		Note:
    */
	public boolean removeSemester(String id){
		this.openDatabase();
		String[] params = {""};
		params[0] = id;
		try{
			myDB.delete("Semester","S_ID=?", params);
		}
		catch(Exception e){
			this.close();
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean removeCourse(String id).
		Function: Xoa mot mon hoc khoi CSDL.
		Parameter: None.
		Return: None.
		Note:
    */
	public boolean removeCourse(String id){
		this.openDatabase();
		String[] params = {""};
		params[0] = id;
		try{
			myDB.delete("Course","C_ID=?", params);
		}
		catch(Exception e){
			// Do something.
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean updateSemesterDetail(Semester semesterInfo).
		Function: Cap nhap thong tin cho mot hoc ki trong CSDL.
		Parameter: Semester semesterInfo: Thong tin cua hoc ki can cap nhap vao CSDL.
		Return: True neu cap nhap thanh cong, nguoc lai la False.
		Note:
    */
	public boolean updateSemesterDetail(Semester semesterInfo){
		this.openDatabase();
		String[] params = {""};
		params[0] = semesterInfo.id;
		ContentValues cv=new ContentValues();
		cv.put("S_Name", semesterInfo.semesterName);
		cv.put("Date_Start", semesterInfo.dateStart);
		cv.put("Date_End", semesterInfo.dateEnd);
		try{
			myDB.update("Semester", cv, "S_ID=?", params);
		}
		catch(Exception e){
			// Do something.
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean updateCourseDetail(Course courseInfo).
		Function: Cap nhap thong tin cho mot mon hoc trong CSDL.
		Parameter: Course courseInfo: Thong tin cua hoc ki can cap nhap vao CSDL.
		Return: True neu cap nhap thanh cong, nguoc lai la False.
		Note:
    */
	public boolean updateCourseDetail(Course courseInfo){
		this.openDatabase();
		String[] params = {""};
		params[0] = courseInfo.id;
		ContentValues cv=new ContentValues();
		cv.put("C_Name", courseInfo.courseName);
		cv.put("Date_Start", courseInfo.dateStart);
		cv.put("Date_End", courseInfo.dateEnd);
		try{
			myDB.update("Course", cv, "C_ID=?", params);
		}
		catch(Exception e){
			// Do something.
			this.close();
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean addNewSemester(Semester semesterInfo).
		Function: Them moi mot hoc ki vao CSDL.
		Parameter: Semester semesterInfo: Thong tin hoc ki can them vao CSDL.
		Return: True neu them HK thanh cong, nguoc lai la False.
		Note:
    */
	public boolean addNewSemester(Semester semesterInfo){
		this.openDatabase();
		ContentValues cv=new ContentValues();
		cv.put("S_Name", semesterInfo.semesterName);
		cv.put("Date_Start", semesterInfo.dateStart);
		cv.put("Date_End", semesterInfo.dateEnd);
		try{
			if(myDB.insert("Semester",null, cv)==-1)
				return false;
		}
		catch(Exception e){
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean addNewCourse(Course courseInfo).
		Function: Them moi mot mon hoc vao CSDL.
		Parameter: Course courseInfo: Thong tin mon hoc can them vao CSDL.
		Return: True neu them MH thanh cong, nguoc lai la False.
		Note:
    */
	public boolean addNewCourse(Course courseInfo){
		this.openDatabase();
		ContentValues cv=new ContentValues();
		cv.put("C_Name", courseInfo.courseName);
		cv.put("S_ID", courseInfo.semesterID);
		cv.put("Date_Start", courseInfo.dateStart);
		cv.put("Date_End", courseInfo.dateEnd);
		try{
			if(myDB.insert("Course",null, cv)==-1)
				return false;
		}
		catch(Exception e){
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean removeAssignment(String id).
		Function: Xoa mot bai tap khoi CSDL.
		Parameter: String id: id cua bai tap can xoa.
		Return: True neu xoa thanh cong, nguoc lai la False.
		Note:
    */
	public boolean removeAssignment(String id){
		this.openDatabase();
		String[] params = {""};
		params[0] = id;
		try{
			myDB.delete("Assignment","A_ID=?", params);
		}
		catch(Exception e){
			// Do something.
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean updateAssignmentDetail(Assignment assInfo).
		Function: Cap nhap thong tin mot bai tap vao CSDL.
		Parameter: Assignment assInfo: Thong tin bai tap can cap nhap.
		Return: True neu cap nhap thanh cong, nguoc lai la False.
		Note:
    */
	public boolean updateAssignmentDetail(Assignment assInfo){
		this.openDatabase();
		String[] params = {""};
		params[0] = assInfo.id;
		ContentValues cv=new ContentValues();
		cv.put("Name", assInfo.name);
		cv.put("Priority", assInfo.priority);
		cv.put("Due_Date", assInfo.dueDate);
		cv.put("Description", assInfo.des);
		cv.put("Alert_Time", assInfo.alertTime);
		cv.put("Done", assInfo.done);
		try{
			myDB.update("Assignment", cv, "A_ID=?", params);
		}
		catch(Exception e){
			// Do something.
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean addNewAssignment(Assignment assignmentInfo).
		Function: Them moi mot bai tap vao CSDL.
		Parameter: Assignment assignmentInfo: Thong tin bai tap can them moi.
		Return: True neu them moi thanh cong, nguoc lai la False.
		Note:
    */
	public boolean addNewAssignment(Assignment assignmentInfo){
		this.openDatabase();
		String done = assignmentInfo.done?"1":"0";
		try{
			myDB.execSQL("INSERT INTO Assignment(Name,Priority,Due_Date,Description,Alert_Time,Done,CL_ID) VALUES(\'"
					+assignmentInfo.name+"\',"
					+assignmentInfo.priority+",\'"
					+assignmentInfo.dueDate+"\',\'"
					+assignmentInfo.des+"\',\'"
					+assignmentInfo.alertTime+"\',"
					+done+","
					+assignmentInfo.classID+");");
		}
		catch(Exception e){
			this.close();
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean removeClass(String id).
		Function: Xoa mot class khoi CSDL.
		Parameter: String id: Id cua class muon xoa khoi CSDL.
		Return: True neu xoa thanh cong, nguoc lai la False.
		Note:
    */
	public boolean removeClass(String id){
		this.openDatabase();
		String[] params = {""};
		params[0] = id;
		try{
			myDB.delete("Class","CL_ID=?", params);
		}
		catch(Exception e){
			// Do something.
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean updateClassDetail(Class classInfo).
		Function: Cap nhap thong tin cho mot lop mon hoc trong CSDL.
		Parameter: Class classInfo: Thong tin cua lop mon hoc muon cap nhap vao CSDL.
		Return: True neu cap nhap thanh cong, nguoc lai tra ve False.
		Note:
    */
	public boolean updateClassDetail(Class classInfo){
		if(classInfo==null)
			return false;
		this.openDatabase();
		String[] params = {""};
		params[0] = classInfo.id;
		ContentValues cv=new ContentValues();
		cv.put("Class_Name", classInfo.className);
		cv.put("C_ID", classInfo.courseID);
		cv.put("Type", classInfo.type);
		cv.put("Location", classInfo.location);
		cv.put("Date_Start", classInfo.dateStart);
		cv.put("Date_End", classInfo.dateEnd);
		cv.put("Time_Start", classInfo.timeStart+":00");
		cv.put("Time_End", classInfo.timeEnd+":00");
		cv.put("Day_repeat", classInfo.dateRepeat);
		cv.put("Repeat", classInfo.repeat);
		cv.put("CT_ID", classInfo.instructorID);
		try{
			myDB.update("Class", cv, "CL_ID=?", params);
		}
		catch(Exception e){
			// Do something.
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean addNewClass(Class classInfo).
		Function: Them moi mot lop mon hoc vao CSDL.
		Parameter: Class classInfo: Thong tin cua lop mon hoc muon them vao CSDL.
		Return: True neu them moi lop mon hoc thanh cong, nguoc lai tra ve False.
		Note:
    */
	public boolean addNewClass(Class classInfo){
		this.openDatabase();
		try{
			myDB.execSQL("INSERT INTO Class(Class_Name,Type,C_ID,CT_ID,Location,Date_Start,Date_End,Time_Start,Time_End,Repeat,Day_repeat) VALUES(\'"
					+classInfo.className+"\',\'"
					+classInfo.type+"\',"
					+classInfo.courseID+","
					+classInfo.instructorID+",\'"
					+classInfo.location+"\',\'"
					+classInfo.dateStart+"\',\'"
					+classInfo.dateEnd+"\',\'"
					+classInfo.timeStart+"\',\'"
					+classInfo.timeEnd+"\',"
					+classInfo.repeat+",\'"
					+classInfo.dateRepeat+"\'"
					+")");
		}
		catch(Exception e){
			this.close();
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public ArrayList<Class> getSemesterClassList(int id).
		Function: Lay ve danh sach lop mon hoc cua mot mon hoc trong hoc ki.
		Parameter: int id: id cua mon hoc can lay danh sach lop mon hoc.
		Return: None.
		Note:
    */
	public ArrayList<Class> getSemesterClassList(int id){
		ArrayList<Class> list = new ArrayList<Class>();
		String[] colums = {"CL_ID","Class_Name","Type","Location","Time_Start","Time_End","Repeat","Day_repeat","Date_Start"};
		String[] parms = {String.valueOf(id)};
		this.openDatabase();
		Cursor result;
		try{
			result = myDB.query("Class", colums,"C_ID", parms, null, null, "Class_Name");
		}
		catch(Exception e){
			return null;
		}
		if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String classId=result.getString(0);
        		String className=result.getString(1);
        		String type=result.getString(2);
        		String location=result.getString(3);
        		String timeStart=result.getString(4);
        		String timeEnd=result.getString(5);
        		String repeat=result.getString(6);
        		String dateRepeat=result.getString(7);
        		int assCount = this.getAssUndoneCount(classId);
        		String dateStart=result.getString(8);
        		list.add(new Class(classId,className,type,location,timeStart,timeEnd,repeat,dateRepeat,dateStart,assCount));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public Semester getSemesterDetail(String id).
		Function: Lay thong tin chi tiet cua mot hoc ki.
		Parameter: String id: id cua hoc ki can lay thong tin chi tiet.
		Return: Doi tuong kieu Semester chua thong tin ve hoc ki can lay.
		Note:
    */
	public Semester getSemesterDetail(String id){
		Semester semester = null;
		String[] colums = {"S_ID","S_Name","Date_Start","Date_End"};
		this.openDatabase();
		String[] parms = {id};
    	Cursor result; 
		try{
			result = myDB.query("Semester", colums, "S_ID=?", parms, null, null, "Date_Start");
		}
		catch(Exception e){
			return null;
		}
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String semesterId = result.getString(0);
        		String name = result.getString(1);
        		String dateStart = result.getString(2);
        		String dateEnd = result.getString(3);
        		semester = new Semester(semesterId,name,dateStart,dateEnd);
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return semester;
	}
	/*
		Method name: public Course getCourseDetail(String id).
		Function: Lay thong tin chi tiet ve mot mon hoc co trong hoc ki.
		Parameter: String id: id cua mon hoc muon lay thong tin chi tiet.
		Return: Doi tuong kieu Course chua thong tin chi tiet cua mot mon hoc co trong hoc ki.
		Note:
    */
	public Course getCourseDetail(String id){
		Course course = null;
		String[] colums = {"C_ID","C_Name","Date_Start","Date_End"};
		this.openDatabase();
		String[] parms = {id};
    	Cursor result; 
		try{
			result = myDB.query("Course", colums, "C_ID=?", parms, null, null, "Date_Start,Date_End,C_Name");
		}
		catch(Exception e){
			return null;
		}
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String courseId = result.getString(0);
        		String name = result.getString(1);
        		String dateStart = result.getString(2);
        		String dateEnd = result.getString(3);
        		course = new Course(courseId,name,dateStart,dateEnd);
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return course;
	}
	/*
		Method name: public ArrayList<Course> getCourseList(String id).
		Function: Lay danh sach mon hoc co trong hoc ki.
		Parameter: String id: id cua hoc ki can lay danh sach mon hoc.
		Return: Danh sach mon hoc co trong hoc ki duoc luu trong 1 bien kieu ArrayList<Course>.
		Note:
    */
	public ArrayList<Course> getCourseList(String id){
		ArrayList<Course> list = new ArrayList<Course>();
		String[] colums = {"C_ID","C_Name","Date_Start","Date_End"};
		String[] parms = {id};
		this.openDatabase();
    	Cursor result; 
		try{
			result = myDB.query("Course", colums, "S_ID=?", parms, null, null, "Date_Start,Date_End,C_Name");
		}
		catch(Exception e){
			return null;
		}
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String courseId = result.getString(0);
        		String name = result.getString(1);
        		String dateStart = result.getString(2);
        		String dateEnd = result.getString(3);
        		list.add(new Course(courseId,name,dateStart,dateEnd));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public ArrayList<Semester> getSemesterList().
		Function: Lay danh sach hoc ki co trong CSDL.
		Parameter: None.
		Return: Tra ve danh sach hoc ki co trong CSDL duoc luu trong bien kieu ArrayList<Semester>.
		Note:
    */
	public ArrayList<Semester> getSemesterList(){
		ArrayList<Semester> list = new ArrayList<Semester>();
		String[] colums = {"S_ID","S_Name","Date_Start","Date_End"};
		this.openDatabase();
    	Cursor result; 
		try{
			result = myDB.query("Semester", colums, null, null, null, null, "Date_Start,Date_End,S_Name");
		}
		catch(Exception e){
			return null;
		}
    	if(result==null){
    		this.close();
    		return null;
    	}
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String semesterId = result.getString(0);
        		String name = result.getString(1);
        		String dateStart = result.getString(2);
        		String dateEnd = result.getString(3);
        		list.add(new Semester(semesterId,name,dateStart,dateEnd));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public Assignment getAssignmentDetail(String id).
		Function: Lay thong tin chi tiet ve mot bai tap.
		Parameter: String id: id cua bai tap can lay thong tin chi tiet.
		Return: Thong tin chi tiet cua mot bai tap.
		Note:
    */
	public Assignment getAssignmentDetail(String id){
		Assignment assDetail = null;
		String[] colums = {"A_ID","Name", "Priority", "Description", "Due_Date","Alert_Time", "Done","CL_ID"};
    	String[] parms = {id};
		this.openDatabase();
    	Cursor result; 
		try{
			result = myDB.query("Assignment", colums, "A_ID=?", parms, null, null, "Done, Name");
		}
		catch(Exception e){
			return null;
		}
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String assId = result.getString(0);
        		String name=result.getString(1);
        		int priority = result.getInt(2);
        		String des = result.getString(3);
        		String dueDate=result.getString(4);
        		String alertTime=result.getString(5);
        		boolean done = result.getString(6).equals("1")?true:false;
        		String classID=result.getString(7);
        		assDetail = new Assignment(assId,name,priority,des,dueDate,alertTime,done,classID);
        		result.moveToNext();
        	}
    	}
        result.close();
        this.close();
        return assDetail;
	}
	/*
		Method name: public ArrayList<Assignment> getStudentAssignmentList(String id).
		Function: Lay danh sach bai tap thuoc mot lop mon hoc.
		Parameter: String id: id cua lop mon hoc muon lay danh sach bai tap.
		Return: None.
		Note:
    */
	public ArrayList<Assignment> getStudentAssignmentList(String id){
		ArrayList<Assignment> list = new ArrayList<Assignment>();
		String[] colums = {"A_ID","Name", "Priority", "Description", "Due_Date", "Done","CL_ID","Alert_Time"};
    	String[] parms = {id};
		this.openDatabase();
    	Cursor result; 
		try{
			result = myDB.query("Assignment", colums, "CL_ID=?", parms, null, null, "Due_Date DESC,Done, Name");
		}
		catch(Exception e){
			return null;
		}
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String assId = result.getString(0);
        		String name=result.getString(1);
        		int priority = result.getInt(2);
        		String des = result.getString(3);
        		String dueDate=result.getString(4);
        		boolean done = result.getString(5).equals("1")?true:false;
        		String classID=result.getString(6);
        		String alertTime=result.getString(7);
        		list.add(new Assignment(assId,name,priority,des,dueDate,alertTime,done,classID));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public Contact getInstructorContactDetail(String id).
		Function: Lay thong tin chi tiet cua mot Contact.
		Parameter: String id: id cua Contact muon lay thong tin chi tiet.
		Return: thong tin chi tiet cua mot Contact.
		Note:
    */
	public Contact getInstructorContactDetail(String id){
		Contact contact = null;
		this.openDatabase();
		String[] colums = {"CT_ID","CT_Name","Email","PhoneNo","Website","Note","Photo","Address","Type"};
    	String[] parms = {""};
    	parms[0] = id;
    	Cursor result = myDB.query("Contact", colums, "CT_ID=?", parms, null, null, null);
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String name=result.getString(1);
        		String email=result.getString(2);
        		String phoneNo=result.getString(3);
        		String website=result.getString(4);
        		String note=result.getString(5);
        		String photo=result.getString(6);
        		String address=result.getString(7);
        		String type=result.getString(8);
        		contact = new Contact(id,name,phoneNo,email,website,note,photo,address,type);
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return contact;
	}
	/*
		Method name: public boolean removeContact(String string).
		Function: Xoa mot contact ra khoi CSDL.
		Parameter: String id: id cua Contact muon xoa ra khoi CSDL .
		Return: None.
		Note:
    */
	public boolean removeContact(String id){
		this.openDatabase();
		String[] params = {""};
		params[0] = id;
		try{
			myDB.delete("Contact","CT_ID=?", params);
		}
		catch(Exception e){
			// Do something.
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean updateContact(Contact contact).
		Function: Cap nhap thong tin chi tiet cua mot Contact.
		Parameter: Contact contact: Thong tin chi tiet cua mot Contact muon cap nhap.
		Return: True neu cap nhap thanh cong nguoc lai tra ve False.
		Note:
    */
	public boolean updateContact(Contact contact){
		this.openDatabase();
		String[] params = {""};
		params[0] = contact.getId();
		ContentValues cv=new ContentValues();
		cv.put("CT_Name", contact.getName());
		cv.put("Email", contact.getEmail());
		cv.put("PhoneNo", contact.getPhoneNo());
		cv.put("Website", contact.getWebsite());
		cv.put("Note", contact.getNote());
		cv.put("Address", contact.getAddress());
		try{
			myDB.update("Contact", cv, "CT_ID=?", params);
		}
		catch(Exception e){
			this.close();
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public boolean removePartner(String assId,String id).
		Function: Xoa mot sinh vien ra khoi danh sach partner cua mot bai tap.
		Parameter: String assId: Id cua bai tap muon xoa partner,String id: Id cua partner muon xoa khoi bai tap.
		Return: None.
		Note:
    */
	public boolean removePartner(String assId,String id){
		this.openDatabase();
		String[] params = {id,assId};
		try{
			myDB.execSQL("DELETE FROM Contact_Assignment WHERE A_ID="+assId+" AND CT_ID="+id);
		}
		catch(Exception e){
			this.close();
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public ArrayList<Contact> getStudentList(String assignmetnId).
		Function: Lay danh sach Partner cua mot bai tap.
		Parameter: String asssignmentId: Id cua bai tap muon lay danh sach Partner.
		Return: Danh sach Partner cung thuc hien bai tap muon lay.
		Note:
    */
	public ArrayList<Contact> getStudentList(String assignmetnId) {
		ArrayList<Contact> list = new ArrayList<Contact>();
		this.openDatabase();
		String[] colums = {"CT_ID","CT_Name","Email","PhoneNo"};
    	String[] parms = {""};
    	parms[0]= "STUDENT";	
    	Cursor result= null;
    	try{
    		//result = myDB.rawQuery("SELECT Contact.CT_ID,CT_NAME FROM Contact, Contact_Assignment WHERE Type!='TEACHER' AND Contact.CT_ID != Contact_Assignment.CT_ID AND Contact_Assignment.A_ID="+assignmetnId+" GROUP BY Contact.CT_ID",null);
    		result = myDB.rawQuery("SELECT CT_ID,CT_NAME FROM Contact"+
    		" WHERE Type='STUDENT' AND CT_ID NOT IN(SELECT CT_ID FROM Contact_Assignment WHERE A_ID= "+assignmetnId+" );",null);
    		
    	}
    	catch(Exception e){
    		this.close();
    		return null;
    	}
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String id = result.getString(0);
        		String name=result.getString(1);
        		list.add(new Contact(id,name));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public boolean addPartner(String assId, String contactId).
		Function: Them mot partner vo danh sach partner cua mot bai tap.
		Parameter: String assId: Id cua bai tap muon them partner, String contactId: Id cua sinh vien muon them vao danh sach partner.
		Return: None.
		Note:
    */
	public boolean addPartner(String assId, String contactId) {
		this.openDatabase();
		ContentValues cv=new ContentValues();
		long id = -1;
		cv.put("A_ID", assId);
		cv.put("CT_ID", contactId);
		try{
			id = myDB.insert("Contact_Assignment",null, cv);
			if(id==-1){
				this.close();
				return false;
			}
		}
		catch(Exception e){
			this.close();
			return false;
		}
		this.close();
		return true;
	}
	/*
		Method name: public ArrayList<Assignment> getAssignmentList(int isDone).
		Function: Lay danh sach bai tap.
		Parameter: int isDone: =0 neu muon lay danh sach bai tap chua hoan tat, 
			=1 neu muon lay danh sach bai tap da hoan tat va gia tri khac neu 
			muon lay danh sach bai tap khong phan biet da hoan tat hay chua.
		Return: None.
		Note:
    */
	public ArrayList<Assignment> getAssignmentList(int isDone){
		String sql;
		if(isDone==0)
			sql = "SELECT A_ID,Name, Priority, Description,"+
			"Due_Date, Done,Assignment.CL_ID,Alert_Time,Class_Name FROM Assignment,Class"+
			" WHERE Assignment.CL_ID = Class.CL_ID AND Done = 0 ORDER BY Due_Date DESC,Name;" ;
		else if(isDone==1){
			sql = "SELECT A_ID,Name, Priority, Description,"+
			"Due_Date, Done,Assignment.CL_ID,Alert_Time,Class_Name FROM Assignment,Class"+
			" WHERE Assignment.CL_ID = Class.CL_ID AND Done = 1 ORDER BY Due_Date DESC,Name;" ;
		}
		else {
			sql = "SELECT A_ID,Name, Priority, Description,"+
			"Due_Date, Done,Assignment.CL_ID,Alert_Time,Class_Name FROM Assignment,Class"+
			" WHERE Assignment.CL_ID = Class.CL_ID ORDER BY Due_Date DESC,Name;";
		}
		ArrayList<Assignment> list = new ArrayList<Assignment>();
		this.openDatabase();
		Cursor result;
		try{
			result = myDB.rawQuery(sql,null);
		}
		catch(Exception e){
			this.close();
			return null;
		}
    	if(result==null){
    		this.close();
    		return null;
    	}
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String assID=result.getString(0);
        		String name=result.getString(1);
        		int priority = result.getInt(2);
        		String des = result.getString(3);
        		String dueDate=result.getString(4);
        		boolean done = result.getString(5).equals("1")?true:false;
        		String classID=result.getString(6);
        		String alertTime=result.getString(7);
        		String className=result.getString(8);
        		list.add(new Assignment(assID,name,priority,des,dueDate,alertTime,done,classID,className));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public ArrayList<Class> getClassOnDate(String date).
		Function: Lay danh sach lop mon hoc trong mot ngay xac dinh.
		Parameter: String date: ngay muon lay danh sach lop mon hoc (Dinh dang yyyy/mm/dd).
		Return: Danh sach lop mon hoc co trong ngay.
		Note:
    */
	public ArrayList<Class> getClassOnDate(String date){
		ArrayList<Class> list = new ArrayList<Class>();
		String[] colums = {"Repeat","Day_repeat","Date_Start","Date_End"}; 
		Date dateGet = MyDate.getDate(date);
		String dateCondition1 = MyDate.getRealYear(dateGet)+"-"+MyDate.getRealMonth(dateGet)+"-01";
		Date tmpDate = dateGet;
		if(tmpDate.getMonth()==12){
			tmpDate.setYear(tmpDate.getYear()+1);
			tmpDate.setMonth(1);
		}
		else{
			tmpDate.setMonth(tmpDate.getMonth()+1);
		}
		String dateCondition2 = MyDate.getRealYear(tmpDate)+"-"+MyDate.getRealMonth(tmpDate)+"-01";
		String[] parms = {dateCondition1,dateCondition1,dateCondition1,dateCondition2};
		this.openDatabase();
		Cursor result;
		String whereClause = " "+
		" ( STRFTIME('%Y-%m',Date_Start) = STRFTIME('%Y-%m',?) ) OR "+
		" ( STRFTIME('%Y-%m',Date_End) = STRFTIME('%Y-%m',?)) OR " +
		" ( Date_Start < ? AND Date_End >= ? )";
		try{
			result = myDB.query("Class", colums, whereClause, parms, null, null, "Time_Start");
		}
		catch(Exception e){
			this.close();
			return null;
		}
		if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String repeat=result.getString(0);
        		String dateRepeat=result.getString(1);
        		String dateStart=result.getString(2);
        		String dateEnd=result.getString(3);
        		list.add(new Class(repeat,dateRepeat,dateStart,dateEnd));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public ArrayList<Assignment> getAssignmentOnMonthList(String date).
		Function: Lay danh sach bai tap den han nop (Due date) trong thang.
		Parameter: String date: Thang muon lay danh sach bai tap (Dinh dang yyyy/mm/dd).
		Return: None.
		Note:
    */
	public ArrayList<Assignment> getAssignmentOnMonthList(String date){
		ArrayList<Assignment> list = new ArrayList<Assignment>();
		this.openDatabase();
		String[] parms = {""};
		parms[0] = date;
		Cursor result;
		try{
			result = myDB.rawQuery("SELECT Due_Date,A_ID FROM Assignment WHERE Done = 0 AND ( STRFTIME('%Y-%m',Due_Date) = STRFTIME('%Y-%m',?) ) ",parms);
		}
		catch(Exception e){
			this.close();
			return null;
		}
    	if(result==null)
    		return null;
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String dueDate=result.getString(0);
        		list.add(new Assignment(dueDate));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public ArrayList<Class> getClassListInDate(String dateStr).
		Function: Lay danh sach lop mon hoc co trong mot ngay xac dinh.
		Parameter: String dateStr(Dinh dang yyyy/mm/dd): ngay muon lay danh sach lop mon hoc.
		Return: Danh sach lop mon hoc co trong mot ngay xac dinh.
		Note:
    */
	public ArrayList<Class> getClassListInDate(String dateStr){
		ArrayList<Class> list = new ArrayList<Class>();
		this.openDatabase();
		Date date = MyDate.getDate(dateStr);
		String weekDay = WeekDay.getWeekDay(date.getDay());
		String[] colums = {"CL_ID,Class_Name","Type","Location","Time_Start","Time_End","Repeat","Day_repeat","Date_Start"};
    	String[] parms = {dateStr,dateStr,weekDay,weekDay,weekDay};	
    	parms[0] = dateStr;    	
    	String whereClause =
    			" ( Date_Start <= ? AND Date_End >= ? ) AND " +
    			" ( " +
    			" Repeat = 0 OR" +
    			" ( Repeat = 2 AND (?!='SUN' OR ?!='SAT')) OR " +
    			" ( Repeat=1 AND Day_repeat=?) " +
    			" ) ";
    	Cursor result = null;
    	try{
    		result = myDB.query("Class", colums, whereClause, parms, null, null, "Class_Name");
    	}
    	catch(Exception e){
    		this.close();
    		return null;
    	}
    	if(result==null){
    		this.close();
    		return null;
    	}
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String id=result.getString(0);
        		String name=result.getString(1);
        		String type=result.getString(2);
        		String location=result.getString(3);
        		String timeStart=result.getString(4).substring(0, 5);
        		String timeEnd=result.getString(5).substring(0, 5);
        		String repeat=result.getString(6);
        		String dateRepeat=result.getString(7);
        		int assCount = this.getAssUndoneCount(id);
        		String dateStart = result.getString(8);
        		list.add(new Class(id,name,type,location,timeStart,timeEnd,repeat,dateRepeat,dateStart,assCount));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
	/*
		Method name: public ArrayList<Assignment> getAssignmentInDateList(String dateStr).
		Function: Lay danh sach bai tap den han nop trong mot ngay xac dinh.
		Parameter: String dateStr(Dinh dang yyyy/mm/dd): Ngay muon lay danh sach bai tap den han.
		Return: Danh sach bai tap den han nop trong mot ngay xac dinh.
		Note:
    */
	public ArrayList<Assignment> getAssignmentInDateList(String dateStr){
		String sql; 
		sql = "SELECT A_ID,Name, Priority, Description,"+
			" Due_Date, Done,Assignment.CL_ID,Alert_Time,Class_Name FROM Assignment,Class"+
			" WHERE Assignment.CL_ID = Class.CL_ID AND Done = 0 AND Due_Date =\'"+dateStr+"\' ORDER BY Name;" ;

		ArrayList<Assignment> list = new ArrayList<Assignment>();
		this.openDatabase();
		Cursor result = null;
		try{
			result = myDB.rawQuery(sql,null);
		}
		catch(Exception e){
			this.close();
			return null;
		}
    	if(result==null){
    		this.close();
    		return null;
    	}
    	else{
        	result.moveToFirst();
        	while (!result.isAfterLast()) {
        		String assID=result.getString(0);
        		String name=result.getString(1);
        		int priority = result.getInt(2);
        		String des = result.getString(3);
        		String dueDate=result.getString(4);
        		boolean done = false;
        		String classID=result.getString(6);
        		String alertTime=result.getString(7);
        		String className=result.getString(8);
        		list.add(new Assignment(assID,name,priority,des,dueDate,alertTime,done,classID,className));
        		result.moveToNext();
        	}
        	result.close();
        	this.close();
    	}
		return list;
	}
}


			